package com.abc.recycling.mapper;

import com.abc.recycling.model.Order;
import com.abc.recycling.domain.statistics.RankingData;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface OrderMapper {
    
    @Insert({
        "INSERT INTO recycling_order (order_no, user_id, collector_id, address, item_type, ",
        "item_description, estimated_weight, actual_weight, price, appointment_time, ",
        "status, cancel_reason, remark, create_time, update_time) ",
        "VALUES (#{orderNo}, #{userId}, #{collectorId}, #{address}, #{itemType}, ",
        "#{itemDescription}, #{estimatedWeight}, #{actualWeight}, #{price}, #{appointmentTime}, ",
        "#{status}, #{cancelReason}, #{remark}, #{createTime}, #{updateTime})"
    })
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int createOrder(Order order);

    @Select("SELECT * FROM recycling_order WHERE id = #{id}")
    Order findById(Long id);

    @Select("SELECT * FROM recycling_order ORDER BY create_time DESC")
    List<Order> getAllOrders();

    @Update({
        "UPDATE recycling_order SET ",
        "collector_id = #{collectorId}, ",
        "status = #{status}, ",
        "actual_weight = #{actualWeight}, ",
        "price = #{price}, ",
        "update_time = #{updateTime} ",
        "WHERE id = #{id}"
    })
    int updateOrder(Order order);

    @Delete("DELETE FROM recycling_order WHERE id = #{id}")
    int deleteOrder(Long id);

    @Select("SELECT * FROM recycling_order WHERE user_id = #{userId} ORDER BY create_time DESC LIMIT #{pageable.offset}, #{pageable.pageSize}")
    Page<Order> getOrdersByUserIdPaged(@Param("userId") Long userId, @Param("pageable") Pageable pageable);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE user_id = #{userId}")
    Long countOrdersByUser(Long userId);

    @Select("SELECT COALESCE(SUM(actual_weight), 0) FROM recycling_order WHERE user_id = #{userId}")
    Double getTotalWeightByUser(Long userId);

    @Select("SELECT AVG(rating) FROM recycling_order WHERE user_id = #{userId} AND rating IS NOT NULL")
    Double getAverageRatingByUser(Long userId);

    @Select("SELECT COALESCE(SUM(price), 0) FROM recycling_order WHERE user_id = #{userId}")
    Double getTotalIncomeByUser(Long userId);

    @Update("UPDATE recycling_order SET rating = #{rating}, comment = #{comment}, update_time = NOW() WHERE id = #{id}")
    int updateOrderRating(@Param("id") Long id, @Param("rating") Integer rating, @Param("comment") String comment);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE status = #{status}")
    Long countOrdersByStatus(String status);

    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, COUNT(*) as count FROM recycling_order GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') ORDER BY date DESC LIMIT 30")
    List<Map<String, Object>> getOrderTrendData();

    @Select("SELECT COUNT(DISTINCT user_id) FROM recycling_order WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)")
    Long countActiveUsers();

    @Select("SELECT COALESCE(SUM(actual_weight), 0) FROM recycling_order")
    Double getTotalWeight();

    @Select("SELECT COALESCE(SUM(price), 0) FROM recycling_order")
    Double getTotalIncome();

    @Select("SELECT * FROM recycling_order WHERE user_id = #{userId} ORDER BY create_time DESC LIMIT 5")
    List<Order> findRecentOrdersByUser(Long userId);

    @Select("SELECT * FROM recycling_order ORDER BY create_time DESC LIMIT #{limit}")
    List<Order> getRecentOrders(int limit);

    @Select("SELECT * FROM recycling_order WHERE order_no = #{orderNo} ORDER BY create_time DESC LIMIT #{pageable.offset}, #{pageable.pageSize}")
    Page<Order> findByOrderNo(@Param("orderNo") String orderNo, @Param("pageable") Pageable pageable);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE DATE(create_time) = CURDATE()")
    Long countTodayOrders();

    @Select("SELECT COALESCE(SUM(actual_weight), 0) FROM recycling_order WHERE DATE(create_time) = CURDATE()")
    Double getTodayTotalWeight();

    @Select("SELECT COUNT(*) FROM recycling_order WHERE status = #{status}")
    Long countByStatus(String status);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE collector_id = #{collectorId} AND create_time >= #{startTime}")
    Long countByCollectorIdAndCreateTimeAfter(Long collectorId, LocalDateTime startTime);

    @Select("SELECT AVG(rating) FROM recycling_order WHERE collector_id = #{collectorId} AND rating IS NOT NULL")
    Double getAverageRatingByCollectorId(Long collectorId);

    @Select("SELECT COALESCE(SUM(actual_weight), 0) FROM recycling_order WHERE create_time BETWEEN #{startTime} AND #{endTime}")
    Double sumWeightByDateRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE create_time BETWEEN #{startTime} AND #{endTime}")
    Long countByDateRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, COUNT(*) as count FROM recycling_order " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') ORDER BY date DESC LIMIT #{limit}")
    List<Map<String, Object>> getTrendData(@Param("startTime") LocalDateTime startTime, 
                                         @Param("endTime") LocalDateTime endTime,
                                         @Param("limit") int limit);

    @Select("SELECT s.name, COUNT(*) as value FROM recycling_order o " +
            "JOIN recycling_station s ON o.station_id = s.id " +
            "WHERE o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY s.id, s.name ORDER BY value DESC LIMIT #{limit}")
    List<RankingData> getStationRanking(@Param("startTime") LocalDateTime startTime,
                                       @Param("endTime") LocalDateTime endTime,
                                       @Param("limit") int limit);

    @Select("SELECT u.name, COUNT(*) as value FROM recycling_order o " +
            "JOIN user u ON o.collector_id = u.id " +
            "WHERE o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY u.id, u.name ORDER BY value DESC LIMIT #{limit}")
    List<RankingData> getCollectorRanking(@Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime,
                                         @Param("limit") int limit);

    @Select("SELECT region, COUNT(*) as count, SUM(actual_weight) as weight, SUM(price) as income " +
            "FROM recycling_order o JOIN recycling_station s ON o.station_id = s.id " +
            "WHERE o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY region ORDER BY count DESC")
    List<Map<String, Object>> getRegionStatistics(@Param("startTime") LocalDateTime startTime,
                                                 @Param("endTime") LocalDateTime endTime);

    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, COUNT(*) as count, " +
            "SUM(actual_weight) as weight, SUM(price) as income " +
            "FROM recycling_order WHERE region = #{region} " +
            "AND create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') ORDER BY date DESC")
    Map<String, Object> getRegionDetail(@Param("region") String region,
                                      @Param("startTime") LocalDateTime startTime,
                                      @Param("endTime") LocalDateTime endTime);

    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, COUNT(*) as count " +
            "FROM recycling_order WHERE region = #{region} " +
            "AND create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') ORDER BY date DESC LIMIT #{limit}")
    List<Map<String, Object>> getRegionTrendData(@Param("region") String region,
                                                @Param("startTime") LocalDateTime startTime,
                                                @Param("endTime") LocalDateTime endTime,
                                                @Param("limit") int limit);

    @Select("SELECT COALESCE(SUM(actual_weight), 0) FROM recycling_order")
    Double sumTotalWeight();

    @Select("SELECT COALESCE(SUM(price), 0) FROM recycling_order")
    Double sumTotalIncome();

    @Select("SELECT COUNT(*) FROM recycling_order WHERE MONTH(create_time) = #{month}")
    Long getMonthlyOrderCount(@Param("month") int month);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE DATE(create_time) = #{date}")
    Long countOrdersByDate(@Param("date") LocalDateTime date);

    @Select("SELECT COALESCE(SUM(actual_weight), 0) FROM recycling_order WHERE DATE(create_time) = #{date}")
    Double sumWeightByDate(@Param("date") LocalDateTime date);

    @Select("SELECT COALESCE(SUM(price), 0) FROM recycling_order WHERE DATE(create_time) = #{date}")
    Double sumIncomeByDate(@Param("date") LocalDateTime date);

    @Select("SELECT COUNT(*) FROM recycling_order WHERE status = 'COMPLETED' AND DATE(create_time) = #{date}")
    Long countCompletedOrdersByDate(@Param("date") LocalDateTime date);

    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, create_time, complete_time)) " +
            "FROM recycling_order WHERE status = 'COMPLETED' AND DATE(create_time) = #{date}")
    Double getAvgProcessTimeByDate(@Param("date") LocalDateTime date);

    @Select("SELECT u.name, COUNT(*) as count FROM recycling_order o " +
            "JOIN user u ON o.collector_id = u.id " +
            "WHERE DATE(o.create_time) = #{date} " +
            "GROUP BY u.id, u.name ORDER BY count DESC LIMIT #{limit}")
    List<Map<String, Object>> getTopCollectorsByDate(@Param("date") LocalDateTime date, @Param("limit") int limit);

    @Select("SELECT s.name, COUNT(*) as count FROM recycling_order o " +
            "JOIN recycling_station s ON o.station_id = s.id " +
            "WHERE DATE(o.create_time) = #{date} " +
            "GROUP BY s.id, s.name ORDER BY count DESC LIMIT #{limit}")
    List<Map<String, Object>> getTopStationsByDate(@Param("date") LocalDateTime date, @Param("limit") int limit);

    @Select("SELECT region, COUNT(*) as count FROM recycling_order o " +
            "JOIN recycling_station s ON o.station_id = s.id " +
            "WHERE DATE(o.create_time) = #{date} " +
            "GROUP BY region ORDER BY count DESC LIMIT #{limit}")
    List<Map<String, Object>> getTopAreasByDate(@Param("date") LocalDateTime date, @Param("limit") int limit);

    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, create_time, complete_time)) " +
            "FROM recycling_order WHERE status = 'COMPLETED' " +
            "AND create_time BETWEEN #{startTime} AND #{endTime}")
    Double getAverageDailyOrdersPerStation(@Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);

    List<Order> findAllWithUserInfo(@Param("pageable") Pageable pageable);
    long countAll();
    long countAllWithUserInfo();
} 